import pandas as pd
import os
from config.config import config_params
import util.env_utils as env_utils
import util.log_utils as log_utils
import util.db_utils as db_utils

__excel_file_path = os.path.join(env_utils.project_root, config_params['excel_file_path'])
__logger = log_utils.setup_logger(env_utils.get_relative_path_and_name(__file__))

def import_to_t_employee_info_from_excel():
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    # 读取 Excel 文件
    df = pd.read_excel(__excel_file_path, sheet_name='人员')
    # 将 DataFrame 中的 NaN 替换为 None
    # df = df.where(pd.notna(df), None)
    v_rows = df.values.tolist()

    v_batch_size = 1000
    for v_i in range(0, len(v_rows), v_batch_size):
        v_batch = v_rows[v_i:v_i + v_batch_size]

        v_insert_query = '''
        INSERT INTO t_employee_info (
            employee_code, employee_name, organization, department, employee_status, job_position
        ) VALUES (%s, %s, %s, %s, %s, %s)
        '''
        try:
            cursor.executemany(v_insert_query, v_batch)
            conn.commit()
            v_end_index = v_i + min(len(v_batch), v_batch_size) - 1
            __logger.info(f'员工信息批次数据导入成功，批次范围：{v_i + 1} - {v_end_index + 1}')
        except Exception as e:
            conn.rollback()
            v_error_row_index = 0
            for v_index, v_row in enumerate(v_batch):
                try:
                    cursor.execute(v_insert_query, v_row)
                except Exception as inner_e:
                    v_error_row_index = v_i + v_index
                    v_end_index = v_i + min(len(v_batch), v_batch_size) - 1
                    __logger.error(f'员工信息批次数据导入失败，当前批次: {v_i // v_batch_size + 1}，开始记录数: {v_i + 1}，结束记录数: {v_end_index + 1}，错误数据所在行: {v_error_row_index + 2}（包含标题行），错误信息: {inner_e}')
                    __logger.error(f'错误的行数据如下： {v_row}')
                    break
            conn.rollback()